Imports System.Data.SQLite

''' <summary>
''' References System.Data.SQLite.dll
''' SQLite open-source database (www.sqlite.org)
''' See also http://sqlite.phxsoftware.com/ and http://system.data.sqlite.org
''' </summary>
Public Class clsDBSQLite
    Inherits clsDB

    ''' <summary>
    ''' Return a copy of the current generic connection
    ''' </summary>
    ''' <returns>Clone of current instance</returns>
    Public Overrides Function Clone() As clsDB
        Dim db As clsDB = New clsDBSQLite
        MyBase.CloneSelf(db)
        Return db
    End Function

    ''' <summary>
    ''' Provider-independent access to CommandBuilder object
    ''' </summary>
    ''' <param name="da">Data adapter</param>
    ''' <returns>CommandBuilder object specific to provider type</returns>
    Public Overrides Function CommandBuilder(ByVal da As DbDataAdapter) As DbCommandBuilder
        Return New SQLiteCommandBuilder(da)
    End Function

    ''' <summary>
    ''' Return a data adapter for the current connection containing data from sql string
    ''' </summary>
    ''' <param name="SqlString">standard sql string</param>
    ''' <returns>Data Adapter</returns>
    Public Overrides Function DataAdapter(ByVal SqlString As String) As DbDataAdapter
        If Connection Is Nothing Then Return Nothing
        Return New SQLiteDataAdapter(SqlString, CType(Connection, SQLiteConnection))
    End Function

    ''' <summary>
    ''' Create a parameter (used to speed up queries and updates); value is initialized to dbnull
    ''' </summary>
    ''' <param name="ParameterName">name of parameter (usually field name)</param>
    ''' <param name="DataType">need to work on this to standarize</param>
    ''' <param name="Size">size if appropriate (strings)</param>
    ''' <returns>Data Parameter</returns>
    Public Overrides Function Parameter(ByRef Command As DbCommand, ByVal ParameterName As String, ByVal DataType As System.Type, Optional ByVal Size As Int16 = 0) As DbParameter
        If Connection Is Nothing Then Return Nothing
        Dim param As SQLiteParameter
        Dim DbType As System.Data.DbType
        If DataType Is GetType(String) Then
            DbType = Data.DbType.String
        ElseIf DataType Is GetType(Date) Then
            DbType = Data.DbType.Date
        ElseIf DataType Is GetType(Double) Then
            DbType = Data.DbType.Double
        ElseIf DataType Is GetType(Integer) Then
            DbType = Data.DbType.Int32
        ElseIf DataType Is GetType(Boolean) Then
            DbType = Data.DbType.Boolean
        ElseIf DataType Is GetType(Short) Then
            DbType = Data.DbType.Int16
        ElseIf DataType Is GetType(Byte()) Then 'test for blob timeseries data
            DbType = Data.DbType.Binary
        Else
            Throw New ApplicationException("Invalid type passed to Parameter")
            Return Nothing
        End If
        'param = New SQLite.SQLiteParameter(ParameterName, DbType, Size)
        'param.Value = DBNull.Value
        param = Command.CreateParameter
        With param
            .ParameterName = ParameterName
            .DbType = DbType
            .Size = Size
            .Value = DBNull.Value
        End With
        Return param
    End Function

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Try
            Dim csb As New SQLiteConnectionStringBuilder
            With csb
                .DataSource = DataSource
                If Not String.IsNullOrEmpty(Password) Then .Password = Password
                'contrary to expectations, following do not improve insert performance
                '.SyncMode = SynchronizationModes.Off
                '.CacheSize = 200000
                Connection = New SQLiteConnection(.ConnectionString)
                'not sure if this really causes a timeout (which would be desired)
                With CType(Connection, SQLiteConnection)
                    .DefaultTimeout = MyBase.CommandTimeout
                End With

            End With
        Catch ex As Exception
            Throw
            Connection = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Creates new database on the provider (uses supervisor access)
    ''' </summary>
    ''' <param name="DatabaseName">Desired name of new database</param>
    ''' <returns>True if successful</returns>
    Public Overrides Function CreateDatabase(ByRef DatabaseName As String) As Boolean
        Try
            SQLite.SQLiteConnection.CreateFile(DataSource)
            Return True
        Catch ex As Exception
            Throw
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Background object for queries so UI doesn't appear to freeze
    ''' </summary>
    Private WithEvents bwExecuteNonQuery As System.ComponentModel.BackgroundWorker

    Private Sub bwExecuteNonQuery_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwExecuteNonQuery.DoWork
        Dim QuerySQL As String = CType(e.Argument, String)
        IsQueryBusy = True
        e.Result = ExecuteNonQuery(QuerySQL)
    End Sub

    Private Sub bwExecuteNonQuery_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwExecuteNonQuery.RunWorkerCompleted
        IsQueryBusy = False
        bwExecuteNonQuery.Dispose()
        bwExecuteNonQuery = Nothing
    End Sub

    ''' <summary>
    ''' Perform database compaction/repair operation where supported
    ''' </summary>
    Public Overrides Sub RepairDatabase(Optional ByVal Interactive As Boolean = False)
        bwExecuteNonQuery = New System.ComponentModel.BackgroundWorker
        Cancelled = False
        With bwExecuteNonQuery
            .WorkerReportsProgress = False
            .WorkerSupportsCancellation = True
            .RunWorkerAsync("VACUUM")
            Do
                Threading.Thread.Sleep(500)
                'System.Windows.Forms.Application.DoEvents()
                If Cancelled Then .CancelAsync()
            Loop Until Not IsQueryBusy
        End With
    End Sub

    ''' <summary>
    ''' Return a list of tables or views for this project
    ''' </summary>
    ''' <returns>List of table or view names</returns>
    Private Function GetTable_ViewList(ByVal IsTable As Boolean) As Generic.List(Of String)
        Dim dr As DbDataReader = Nothing
        Try
            If Not OpenConnection() Then Return Nothing

            Dim col As New Generic.List(Of String)
            dr = ExecuteReader("SELECT name FROM sqlite_master WHERE type LIKE '{0}'", IIf(IsTable, "table", "view"))
            While dr IsNot Nothing AndAlso dr.Read
                col.Add(Proper(dr.GetString(0)))
            End While
            Return col
        Catch ex As Exception
            Throw
            Return Nothing
        Finally
            If dr IsNot Nothing Then dr.Close()
            CloseConnection()
        End Try
    End Function

    ''' <summary>
    ''' Return a list of  tables for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overrides Function GetTableList() As Generic.List(Of String)
        Return GetTable_ViewList(True)
    End Function

    ''' <summary>
    ''' Return a list of views for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overrides Function GetViewList() As Generic.List(Of String)
        Return GetTable_ViewList(False)
    End Function

    Private WithEvents bwGetRecordCount As System.ComponentModel.BackgroundWorker
    Private RecordCount As Integer

    ' ''' <summary>
    ' ''' Determine the number of records in a table
    ' ''' </summary>
    ' ''' <param name="TableName">Name of table</param>
    ' ''' <returns>Number of records</returns>
    ' ''' <remarks>CommandTimeout does not appear to work for SQLite; wrap in thread</remarks>
    'Public Overrides Function GetRecordCount(ByVal TableName As String, Optional ByVal CommandTimeout As Integer = 10) As Integer
    '    bwGetRecordCount = New System.ComponentModel.BackgroundWorker
    '    With bwGetRecordCount
    '        .WorkerSupportsCancellation = True
    '        .RunWorkerAsync(TableName)
    '        Dim t1 As Date = Now
    '        Do
    '            Threading.Thread.Sleep(500)
    '            System.Windows.Forms.Application.DoEvents()
    '            If Cancelled Or Now.Subtract(t1).TotalSeconds > CommandTimeout Then .CancelAsync()
    '        Loop Until Not IsQueryBusy
    '        Return RecordCount
    '    End With
    'End Function

    Private Sub bwGetRecordCount_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwGetRecordCount.DoWork
        If MyBase.Connection Is Nothing Then e.Result = Nothing : Exit Sub
        If Not OpenConnection() Then e.Result = 0 : Exit Sub
        Dim cmd As DbCommand
        cmd = MyBase.Connection.CreateCommand
        cmd.CommandText = String.Format("SELECT COUNT(*) FROM {0}", FullTableName(e.Argument))
        IsQueryBusy = True
        e.Result = cmd.ExecuteScalar()
    End Sub

    Private Sub bwGetRecordCount_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwGetRecordCount.RunWorkerCompleted
        If e.Error IsNot Nothing Then
            Throw e.Error
        ElseIf e.Cancelled Then
            RecordCount = -1
        Else
            RecordCount = e.Result
        End If
        IsQueryBusy = False
        bwGetRecordCount.Dispose()
        bwGetRecordCount = Nothing
        MyBase.Connection.Close()
    End Sub
End Class
